package com.study.jdbc.junit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.UUID;

import org.junit.Test;

import com.study.jdbc.utils.DBUtil;

/**
* @Name: BatchExecuteTest
* @Description: sql语句批处理测试类
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-17 下午8:24:16 
* @Version: V1.0
 */
public class BatchExecuteTest {
	
	/**
	* @Name: batchExecute01
	* @Description: 批处理：插入三条数据，删除一条数据
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-17 下午8:30:50
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void batchExecute01() throws Exception {
		Connection con = null ;
		Statement s = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql01 = "insert into t_batch(id, content) values(1, '1') ; " ;
			String sql02 = "insert into t_batch(id, content) values(2, '2') ; " ;
			String sql03 = "insert into t_batch(id, content) values(3, '3') ; " ;
			String sql04 = "delete from t_batch where id = 1 ; " ;
			//创建sql语句执行对象
			s = con.createStatement() ;
			//添加批处理sql语句
			s.addBatch(sql01) ;
			s.addBatch(sql02) ;
			s.addBatch(sql03) ;
			s.addBatch(sql04) ;
			//批量执行sql语句
			int[] result = s.executeBatch() ;
			if(result != null && result.length > 0) {
				System.out.println("批处理执行成功！");
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			//释放资源
			DBUtil.close(con, s, null) ;
		}
	}
	
	/**
	* @Name: batchExecute02
	* @Description: 批量插入1万条数据
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-17 下午8:38:33
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void batchExecute02() {
		Connection con = null ;
		PreparedStatement ps = null ;
		try {
			//获取数据库连接对象
			con = DBUtil.getConnection() ;
			String sql = "insert into t_batch(id, content) values(?, ?) ; " ;
			//创建sql语句执行对象
			ps = con.prepareStatement(sql) ;
			//批量插入1万条数据
			for (int i = 0; i < 10000; i++) {
				ps.setString(1, UUID.randomUUID().toString()) ;
				ps.setString(2, "batch-" + i) ;
				ps.addBatch() ;
				if(i % 1000 == 0) {
					ps.executeBatch() ;
					ps.clearBatch() ;
				}
			}
			ps.executeBatch() ;
			System.out.println("插入成功！");
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally {
			DBUtil.close(con, ps, null) ;
		}
	}

}








